#!/usr/bin/env groovy
package MergeSqlUtil

//purpose: use merge instead of insert into _load table then proc_metadata_copy
//proc_metadata_copy won't work when updating existing rows, it only work when insert new rows

//TODO: distribution_Id, next value, shouldn't be in update clause

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

import java.text.DecimalFormat;
import java.text.NumberFormat;


ArrayList<String> convertWorkbookToSQL(HSSFWorkbook workbook)
{
	ArrayList<String> sqlStatements = new ArrayList<String>();
	int sheets = workbook.getNumberOfSheets();
	for(int i = 0; i < sheets; i++)
	{
		HSSFSheet sheet = workbook.getSheetAt(i);
		ArrayList<String[]> sheetData = sheetToArrayList(sheet);
		if(sheetData.size() == 0) {
			continue;
		}

        //column row, header row
		String[] columns = sheetData[0]
		
		for(int j = 1; j < sheetData.size(); j++) {
			String result = toSQLInsertStatement(sheet.getSheetName(), sheetData[j], columns);
			sqlStatements.add(result);
		}
	}
	return sqlStatements
}


//read each row and convert to insert
ArrayList<String> convertSheetToSQL(HSSFSheet sheet) {
	ArrayList<String> sqlStatements = new ArrayList<String>();
	ArrayList<String[]> sheetData = sheetToArrayList(sheet);

	String[] columns = sheetData[0]
	
	for(int j = 1; j < sheetData.size(); j++) {
        String result = toMergeSQLStatement(sheet.getSheetName(), sheetData[j], columns);
        println("get [$j]: $result")
		sqlStatements.add(result);
	}
	return sqlStatements
}

//
ArrayList<String[]> sheetToArrayList(HSSFSheet sheet) {
	ArrayList<String[]> sheetData = new ArrayList<String[]>();
	
	int rowLength = 0;
	int j = 0;
	while(true) {
		HSSFRow row = sheet.getRow(j);
		if(row == null)  {
			break;
		}
		
		if(j == 0)  {
			rowLength = row.getLastCellNum();
		}

		String[] rowData = new String[rowLength];
		int nullCounter = 0;
		for(int k = 0; k < rowLength; k++) {
			HSSFCell cell = row.getCell(k);
			
			if(cell == null) {
				rowData[k] = "NULL";
			} else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
				Double num = cell.getNumericCellValue();
				NumberFormat nf = new DecimalFormat("0.###")
				String num2 = nf.format(num)
				rowData[k] = num2
			} else {
				rowData[k] = cell.toString().replaceAll("\n", "").replaceAll("\r", "");
			}
				
			if(rowData[k].equals("") || rowData[k].equals("NULL"))
			{
				nullCounter++
			}
		}
		
		j++;
		if(nullCounter != rowLength)
		{
			sheetData.add(rowData);
		} else
		{
			break;
		}
	}
	return sheetData;
}

String toMergeSQLStatement(String tableName, String[] row, String[] columns) {
    tableName = tableName.toUpperCase()
    def br = '\n'
    def tab = '\t'
    // maps that need check columns other than the 1st column in merge
    def checkCols = [
            'JOB_SET_TASK_LOAD':2,
            'TASK_PARAMETERS':2,
            'JOB_AUTO_QUEUE':2,
            'MUTEX_JOB_SETS':2,
            'JOB_SET_DEPENDENCY':2,
            'ETL_NODE_STREAM':2,
    ]
    //'EXTRACTOR_COMPANY_CONFIG':4,
    println("get: " + checkCols.get("TASK_PARAMETERS"))
    StringBuilder sqlStmt = new StringBuilder()
    sqlStmt.append("MERGE INTO ")
    sqlStmt.append(tableName.toUpperCase())
    sqlStmt.append(" D ${br} USING ( ${br}${tab}SELECT ${br}");
    for(int i = 0; i < row.size(); i++) {
        if(i > 0) {
            sqlStmt.append(",${br}")
        }
        if(row[i].toUpperCase().equals("NULL") || row[i].toUpperCase().equals("(NULL)")) {
            sqlStmt.append("${tab}${tab}NULL as \"" + columns[i] + "\"")
        } else if(row[i].isNumber()) {
            sqlStmt.append("${tab}${tab}" + row[i])
        } else if (row[i].startsWith("(") && row[i].endsWith(")")){
            //don't need quote to wrap
            sqlStmt.append("${tab}${tab}" + row[i])
            sqlStmt.append(" as \"")
            sqlStmt.append(columns[i])
            sqlStmt.append("\"")
        } else if(row[i].equalsIgnoreCase('sysdate')) {
            sqlStmt.append("${tab}${tab}sysdate")
            sqlStmt.append(" as \"")
            sqlStmt.append(columns[i])
            sqlStmt.append("\"")
        } else {
            def escSQuote = row[i].replace("'", "''")
            sqlStmt.append("${tab}${tab}'")
            sqlStmt.append(escSQuote)
            sqlStmt.append("' as \"")
            sqlStmt.append(columns[i])
            sqlStmt.append("\"")
        }
    }
    sqlStmt.append("${br} FROM DUAL) S ${br}")
    sqlStmt.append("ON (${br}")

    def needCheckCount = checkCols.get(tableName)?checkCols.get(tableName):1
    if (needCheckCount > 1){
        //need check >1 columns
       for (int i = 0; i < needCheckCount; i++){
           if (i>0 ) sqlStmt.append(" AND ")
           sqlStmt.append("${tab}S." + columns[i] + " = D." + columns[i] )
       }
    }else{
       //only check first column
        sqlStmt.append("${tab}S." + columns[0] + " = D." + columns[0] )
    }
    sqlStmt.append(")${br}WHEN MATCHED THEN ${br}${tab}UPDATE SET ${br}")

    //put rest of columns into update set part
    for (int i = needCheckCount; i < columns.length; i++){
        sqlStmt.append("${tab}${tab}D." + columns[i] + " = S." + columns[i] )
        if (i>0 && i<columns.length-1) sqlStmt.append(", ${br}")
    }

    sqlStmt.append("${br}WHEN NOT MATCHED THEN ${br}${tab}INSERT (${br}${tab}${tab}")
    for(int i = 0; i < columns.length; i++) {
        if(i > 0)  sqlStmt.append(", ")
        sqlStmt.append(columns[i])
    }
    sqlStmt.append(") ${br}${tab}VALUES ( ${br}${tab}${tab}")

    for(int i = 0; i < columns.length; i++){
        if (i>0) sqlStmt.append( ", ")
        sqlStmt.append("S." + columns[i] )
    }
    sqlStmt.append(");")

    return sqlStmt.toString()
}

String toSQLInsertStatement(String tableName, String[] row, String[] columns) {
	StringBuilder sqlStmt = new StringBuilder()
	sqlStmt.append("INSERT INTO ")
	sqlStmt.append(tableName)
	sqlStmt.append(" (");
	for(int i = 0; i < columns.length; i++) {
		if(i > 0)
		{
			sqlStmt.append(", ")
		}
		sqlStmt.append(columns[i])
	}
	sqlStmt.append(")");
	sqlStmt.append(" VALUES (")
	for(int i = 0; i < row.size(); i++)
	{
		if(i > 0)
		{
			sqlStmt.append(", ")
		}
		if(row[i].toUpperCase().equals("NULL") || row[i].toUpperCase().equals("(NULL)"))
		{
			sqlStmt.append("NULL")
		} else if(row[i].isNumber())
		{
			sqlStmt.append(row[i])
		} else
		{
			def escSQuote = row[i].replace("'", "''")
			sqlStmt.append("'")
			sqlStmt.append(escSQuote)
			sqlStmt.append("'")
		}
		
	}
	sqlStmt.append(");")

	return sqlStmt.toString()
}

def cli = new CliBuilder( usage: "${this.class.name}.groovy [options...] [<file1> [<file2> [<file3>]...]]" )

cli.h( longOpt: 'help', 'usage information' )

def opt = cli.parse( args )

if ( opt.h )
{
    cli.usage()
    System.exit 1
}
if(opt.arguments().size() == 0)
{
	println("Please provide an input file.")
    cli.usage()
    System.exit 1
}

def files = opt.arguments()

def filename = files[0]

InputStream inp = new FileInputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook(inp);
int sheets = wb.getNumberOfSheets();

Date date = new Date()
StringBuilder today = new StringBuilder()
Formatter dateform = new Formatter(today, Locale.US);
dateform.format("%tY%tm%td%tH%tM", date, date, date, date, date)
dateform.close()

for(int i = 0; i < sheets; i++)
{
	HSSFSheet sheet = wb.getSheetAt(i);
    //TODO: remove later
//    if (sheet.getSheetName().toUpperCase() != "ETL_CYCLE_LOAD") continue;

	ArrayList<String> results = convertSheetToSQL(sheet);
	println(sheet.getSheetName())
	def output = new File(sheet.getSheetName() + "-" + today + ".sql")
	println(output.getName())
	output.write("")
	results.each {
	    output.append("${it}\n")
	}
}
